package com.example.guesswho.data.local

import androidx.room.*
import kotlinx.coroutines.flow.Flow

@Dao
interface QuestionDao {
    @Query("SELECT * FROM questions WHERE isAudited = 1 ORDER BY difficulty ASC, category ASC")
    fun getAllAuditedQuestions(): Flow<List<QuestionEntity>>
    
    @Query("SELECT * FROM questions WHERE category = :category AND isAudited = 1")
    fun getQuestionsByCategory(category: String): Flow<List<QuestionEntity>>
    
    @Query("SELECT * FROM questions WHERE difficulty = :difficulty AND isAudited = 1")
    fun getQuestionsByDifficulty(difficulty: Int): Flow<List<QuestionEntity>>
    
    @Query("SELECT * FROM questions WHERE objectId = :objectId")
    suspend fun getQuestionById(objectId: String): QuestionEntity?
    
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertQuestions(questions: List<QuestionEntity>)
    
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertQuestion(question: QuestionEntity)
    
    @Update
    suspend fun updateQuestion(question: QuestionEntity)
    
    @Delete
    suspend fun deleteQuestion(question: QuestionEntity)
    
    @Query("DELETE FROM questions")
    suspend fun clearAllQuestions()
    
    @Query("SELECT * FROM questions WHERE lastSyncedAt < :cutoffDate")
    suspend fun getStaleQuestions(cutoffDate: java.util.Date): List<QuestionEntity>
}

@Dao
interface RoomDao {
    @Query("SELECT * FROM rooms ORDER BY createdAt DESC")
    fun getAllRooms(): Flow<List<RoomEntity>>
    
    @Query("SELECT * FROM rooms WHERE status = :status")
    fun getRoomsByStatus(status: String): Flow<List<RoomEntity>>
    
    @Query("SELECT * FROM rooms WHERE objectId = :objectId")
    suspend fun getRoomById(objectId: String): RoomEntity?
    
    @Query("SELECT * FROM rooms WHERE difficulty = :difficulty AND status = 'waiting'")
    fun getAvailableRoomsByDifficulty(difficulty: Int): Flow<List<RoomEntity>>
    
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertRooms(rooms: List<RoomEntity>)
    
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertRoom(room: RoomEntity)
    
    @Update
    suspend fun updateRoom(room: RoomEntity)
    
    @Delete
    suspend fun deleteRoom(room: RoomEntity)
    
    @Query("DELETE FROM rooms WHERE status = 'ended' AND createdAt < :cutoffDate")
    suspend fun deleteOldEndedRooms(cutoffDate: java.util.Date)
}

@Dao
interface AudienceChatDao {
    @Query("SELECT * FROM audience_chats WHERE roomId = :roomId ORDER BY ts DESC")
    fun getChatsByRoomId(roomId: String): Flow<List<AudienceChatEntity>>
    
    @Query("SELECT * FROM audience_chats WHERE roomId = :roomId ORDER BY ts DESC LIMIT :limit")
    suspend fun getRecentChatsByRoomId(roomId: String, limit: Int): List<AudienceChatEntity>
    
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertChat(chat: AudienceChatEntity)
    
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertChats(chats: List<AudienceChatEntity>)
    
    @Query("DELETE FROM audience_chats WHERE roomId = :roomId")
    suspend fun clearChatsForRoom(roomId: String)
    
    @Query("DELETE FROM audience_chats WHERE ts < :cutoffTimestamp")
    suspend fun deleteOldChats(cutoffTimestamp: Long)
}

@Dao
interface ShortLinkDao {
    @Query("SELECT * FROM short_links WHERE code = :code")
    suspend fun getShortLinkByCode(code: String): ShortLinkEntity?
    
    @Query("SELECT * FROM short_links WHERE roomId = :roomId")
    suspend fun getShortLinksByRoomId(roomId: String): List<ShortLinkEntity>
    
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertShortLink(shortLink: ShortLinkEntity)
    
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertShortLinks(shortLinks: List<ShortLinkEntity>)
    
    @Update
    suspend fun updateShortLink(shortLink: ShortLinkEntity)
    
    @Delete
    suspend fun deleteShortLink(shortLink: ShortLinkEntity)
    
    @Query("DELETE FROM short_links WHERE createdAt < :cutoffDate")
    suspend fun deleteExpiredShortLinks(cutoffDate: java.util.Date)
}